package com.open.generate.database;

import com.open.base.dispatch.IGeneralConfigDispatch;
import com.open.base.model.DataSource;
import com.open.base.model.dto.UserConfigDto;
import com.open.common.utils.StringUtils;
import com.open.generate.model.ColumnData;
import com.open.generate.model.TableData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import static com.open.common.enums.ConfigEnum.DataBase;

/**
 * @author ：dashu03
 * @create ：2025/3/29 14:06
 * @description ：数据库查询操作
 */
@Component
public class MysqlSelectProperty{

    @Autowired
    private IGeneralConfigDispatch userConfigDispatch;

    private DataSource dataSource;

    /**
     * 根据数据库名分页查询数据库表信息总条数
     * @param map
     * @return Integer
     * @throws SQLException
     */
    public Integer findPageCount(Map<String, Object> map) throws SQLException {
        int count = 0;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            // 获取数据源和连接。
            // 进页面/或更新数据都会掉用该方法，缓冲数据减少查询。
            dataSource = (DataSource) userConfigDispatch.getUserConfig(new UserConfigDto(DataBase.getType()));
            con = DBConnection.getConnection(dataSource);
            String databaseName = getDatabaseNameFromUrl(dataSource.getUrl());

            // 构建 SQL 和参数（不包含分页条件）
            StringBuilder sql = new StringBuilder(DBTypeToBeanMapping.SQLCOUNT);
            List<Object> params = new ArrayList<>();
            params.add(databaseName);

            // 动态拼接查询条件
            String tableName = map.get("tableName").toString();
            if (StringUtils.isNotEmpty(tableName)) {
                sql.append(" AND table_name LIKE ?");
                params.add("%" + tableName + "%");
            }
            String tableComment = map.get("tableComment").toString();
            if (StringUtils.isNotEmpty(tableComment)) {
                sql.append(" AND table_comment LIKE ?");
                params.add("%" + tableComment + "%");
            }

            // 创建 PreparedStatement 并设置参数
            ps = con.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }

            // 执行查询并处理结果
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
            return count;
        } finally {
            DBConnection.closeResource(con, ps, rs);
        }
    }

    /**
     * 根据数据库名分页查询数据库表信息
     * @param map
     * @return List<TableData>
     * @throws SQLException
     */
    public List<TableData> findPage(Map<String, Object> map) throws SQLException {
        List<TableData> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            if (dataSource == null) {
                dataSource = (DataSource) userConfigDispatch.getUserConfig(new UserConfigDto(DataBase.getType()));
            }
            con = DBConnection.getConnection(dataSource);
            String databaseName = getDatabaseNameFromUrl(dataSource.getUrl());

            // 构建 SQL 和参数
            StringBuilder sql = new StringBuilder(DBTypeToBeanMapping.SQLTABLES);
            List<Object> params = new ArrayList<>();
            params.add(databaseName);

            // 动态拼接查询条件
            String tableName = map.get("tableName").toString();
            if (StringUtils.isNotEmpty(tableName)) {
                sql.append(" AND table_name LIKE ?");
                params.add("%" + tableName + "%");
            }
            String tableComment = map.get("tableComment").toString();
            if (StringUtils.isNotEmpty(tableComment)) {
                sql.append(" AND table_comment LIKE ?");
                params.add("%" + tableComment + "%");
            }

            int startPoint = (int) map.get("startPoint");
            int endPoint = (int) map.get("endPoint");
            sql.append(" LIMIT ?, ?");
            params.add(startPoint);
            params.add(endPoint);

            // 创建 PreparedStatement 并设置参数
            ps = con.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }

            // 执行查询并处理结果
            rs = ps.executeQuery();
            while (rs.next()) {
                TableData tableData = new TableData();
                tableData.setTableName(rs.getString(1));
                tableData.setTableComment(rs.getString(2));
                tableData.setCreatTime(rs.getString(3));
                tableData.setUpdateTime(rs.getString(4));
                list.add(tableData);
            }
            return list;
        } finally {
            DBConnection.closeResource(con, ps, rs);
        }
    }

    // 提取数据库名称的方法
    public String getDatabaseNameFromUrl(String url) {
        int startIndex = url.indexOf("/", url.indexOf("//") + 2) + 1;
        int endIndex = url.indexOf("?", startIndex);
        if (endIndex == -1) {
            endIndex = url.length();
        }
        return url.substring(startIndex, endIndex);
    }

    /**
     * 查询数据库表所有的列
     * @param databaseName tableName
     * @return List<ColumnData>
     * @throws SQLException
     */
    public List<ColumnData> queryColumnDatas(String databaseName, String tableName) throws SQLException {
        List<ColumnData> columnList = new ArrayList<ColumnData>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            if (dataSource == null) {
                dataSource = (DataSource) userConfigDispatch.getUserConfig(new UserConfigDto(DataBase.getType()));
            }
            con = DBConnection.getConnection(dataSource);
            ps = con.prepareStatement(DBTypeToBeanMapping.SQLCOLUMNS);
            ps.setString(1, databaseName);
            ps.setString(2, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                String name = rs.getString(1);
                String type = rs.getString(2);
                String comment = rs.getString(3);
                Boolean required = Objects.equals(rs.getString(4), "NO");// required
                String jdbcType = DBTypeToBeanMapping.convertType(type);
                String propertyName = DBTypeToBeanMapping.columnProperty(name);
                ColumnData cd = new ColumnData();
                cd.setColumnName(name);
                cd.setDataType(type);
                cd.setJdbcType(jdbcType);
                cd.setColumnComment(comment);
                cd.setPropertyName(propertyName);
                cd.setRequired(required);
                columnList.add(cd);
            }
        } finally {
            DBConnection.closeResource(con, ps, rs);
        }
        return columnList;
    }

    /**
     * 查询当前表的主键
     * @param tableName databaseName
     * @return Map<String,ColumnData>
     * @throws SQLException
     */
    public Map<String, ColumnData> queryTablePeKey(String tableName, String databaseName) throws SQLException {
        Map<String, ColumnData> priyKey = new HashMap<String, ColumnData>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            if (dataSource == null) {
                dataSource = (DataSource) userConfigDispatch.getUserConfig(new UserConfigDto(DataBase.getType()));
            }
            con = DBConnection.getConnection(dataSource);
            ps = con.prepareStatement(DBTypeToBeanMapping.SQL_PRIMARY_KEY);
            ps.setString(1, tableName);
            ps.setString(2, databaseName);
            rs = ps.executeQuery();
            while (rs.next()) {
                String name = rs.getString(1);
                String type = rs.getString(2);
                String comment = rs.getString(3) + "";
                String auto = rs.getString(4) + "";
                String jdbcType = DBTypeToBeanMapping.convertType(type);
                ColumnData cd = new ColumnData();
                cd.setColumnName(name);
                cd.setDataType(type);
                cd.setJdbcType(jdbcType);
                cd.setColumnComment(comment.replaceAll("\n", ""));
                if ("auto_increment".equalsIgnoreCase(auto)) {
                    cd.setAuto(true);
                }
                String propertyName = DBTypeToBeanMapping.columnProperty(name);
                cd.setPropertyName(propertyName);
                String maxChar = propertyName.substring(0, 1).toUpperCase();
                String method = "get" + maxChar + propertyName.substring(1, propertyName.length());
                cd.setPkget(method);
                priyKey.put(name, cd);
            }
        } finally {
            DBConnection.closeResource(con, ps, rs);
        }
        return priyKey;
    }
}
